package com.simoniu.statement.preparedstatementdemo;

import com.simoniu.entity.Students;
import com.simoniu.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class PreparedStatementDemo {

    //查询所有的女学生
    public List<Students> queryStudentsByGender(String gender) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "";
        List<Students> studentsList = new ArrayList<Students>();
        try {
            conn = DBUtil.getConnection();
            if (conn != null) {
                // 查询所有的女学生
                sql = "select * from students where gender=?;";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, gender);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    //System.out.println(rs.getInt("sid") + "," + rs.getString("sname") + "," + rs.getString("gender"));
                    Students s = new Students();
                    s.setSid(rs.getInt("sid"));
                    s.setSname(rs.getString("sname"));
                    s.setGender(rs.getString("gender"));
                    studentsList.add(s);
                }
                return studentsList;
            }
            return null;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pstmt != null) {
                    pstmt.close();
                    pstmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    //批量新增
    public boolean addBatch(List<Students> studentsList) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "";
        try {
            conn = DBUtil.getConnection();
            if (conn != null) {
                sql = "insert into students (sname,gender) values (?,?);";
                pstmt = conn.prepareStatement(sql);
                for (Students s : studentsList) {
                    pstmt.setString(1, s.getSname());
                    pstmt.setString(2, s.getGender());
                    pstmt.executeUpdate();
                }
                return true;
            }
            return false;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pstmt != null) {
                    pstmt.close();
                    pstmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        List<Students> studentsList = new ArrayList<Students>();
        studentsList.add(new Students(100, "刘备", "男"));
        studentsList.add(new Students(100, "关羽", "男"));
        studentsList.add(new Students(100, "张飞", "男"));

        PreparedStatementDemo demo = new PreparedStatementDemo();
        List<Students> studentsList1 =  demo.queryStudentsByGender("女");
        if(studentsList1!=null){
            studentsList1.forEach(System.out::println);
        }else{
            System.out.println("查询失败！");
        }

        /*
        if(demo.addBatch(studentsList)){
            System.out.println("批量新增学生资料成功！");
        }else{
            System.out.println("批量新增学生资料失败！");
        }*/
    }

}
